package by.grsu.crs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import by.grsu.crs.dao.ConfidentialDataDao;
import by.grsu.crs.dao.UserDao;
import by.grsu.crs.model.ConfidentialData;
import by.grsu.crs.model.User;

public class UserDaoImpl implements UserDao {
	private static final String INSERT_NEW_USER
		= "INSERT INTO users(email, password, cdid, role) VALUES (?, MD5(?), ?, ?)";
	
	private static final String SELECT_ALL_USERS
		= "SELECT id, email, password, cdid, role FROM users";
	
	private static final String SELECT_USER_BY_ID
		= "SELECT id, email, password, cdid, role FROM users WHERE id = ?";
	
	private static final String SELECT_USER_BY_EMAIL
		= "SELECT id, email, password, cdid, role FROM users WHERE email = ?";
	
	private static final String SELECT_USER_BY_EMAIL_AND_PASSWORD
	= "SELECT id, email, password, cdid, role FROM users WHERE email = ? AND password = MD5(?)";
	
	private static final String DELETE_USER_BY_ID
		= "DELETE FROM users WHERE id = ?;";
	
	private static final String UPDATE_USER_BY_ID
		= "UPDATE users SET email = ?, password = MD5(?), cdid = ?, role = ? WHERE id = ?";
	
	private Connection dbConnection;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public UserDaoImpl(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}
	
	public boolean addUser(User user) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(INSERT_NEW_USER);
			preparedStatement.setString(1, user.getEmail());
			preparedStatement.setString(2, user.getPassword());
			if(user.getConfidentialData() != null) {
				preparedStatement.setInt(3, user.getConfidentialData().getId());
			} else {
				preparedStatement.setNull(3, Types.INTEGER);
			}
			preparedStatement.setString(4, user.getRole().toString());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
	
	public List<User> getAllUsers() throws SQLException {
		List<User> users = new ArrayList<User>();
		ConfidentialDataDao confidentialDataDao = new ConfidentialDataDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ALL_USERS);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				users.add(new User(resultSet.getInt("id"), 
						resultSet.getString("email"), 
						resultSet.getString("password"), 
						confidentialDataDao.getConfidentialDataById(resultSet.getInt("cdid")),
						User.Role.valueOf(resultSet.getString("role"))));
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return users;
	}
	
	public User getUserById(int id) throws SQLException {
		User user = null;
		ConfidentialDataDao confidentialDataDao = new ConfidentialDataDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_USER_BY_ID);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				String email = resultSet.getString("email");
				String password = resultSet.getString("password");
				ConfidentialData confidentialData = confidentialDataDao.getConfidentialDataById(resultSet.getInt("cdid"));
				User.Role role = User.Role.valueOf(resultSet.getString("role"));
				
				user = new User(id, email, password, confidentialData, role);
			}		
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return user;
	}
	
	public User getUserByEmail(String email) throws SQLException {
		User user = null;
		ConfidentialDataDao confidentialDataDao = new ConfidentialDataDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_USER_BY_EMAIL);
			preparedStatement.setString(1, email);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int id = resultSet.getInt("id");
				String password = resultSet.getString("password");
				ConfidentialData confidentialData = confidentialDataDao.getConfidentialDataById(resultSet.getInt("cdid"));
				User.Role role = User.Role.valueOf(resultSet.getString("role"));
				
				user = new User(id, email, password, confidentialData, role);
			}		
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return user;
	}
	
	public boolean deleteUserByID(int id) throws SQLException {		
		try {
			preparedStatement = dbConnection.prepareStatement(DELETE_USER_BY_ID);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
	
	public boolean updateUserById(int id, User user) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(UPDATE_USER_BY_ID);
			preparedStatement.setString(1, user.getEmail());
			preparedStatement.setString(2, user.getPassword());
			if(user.getConfidentialData() != null) {
				preparedStatement.setInt(3, user.getConfidentialData().getId());
			} else {
				preparedStatement.setNull(3, Types.INTEGER);
			}
			preparedStatement.setString(4, user.getRole().toString());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

	public User getUserByEmailAndPassword(String email, String password)
			throws SQLException {
		User user = null;
		ConfidentialDataDao confidentialDataDao = new ConfidentialDataDaoImpl(dbConnection);
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_USER_BY_EMAIL_AND_PASSWORD);
			preparedStatement.setString(1, email);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int id = resultSet.getInt("id");
				ConfidentialData confidentialData = confidentialDataDao.getConfidentialDataById(resultSet.getInt("cdid"));
				User.Role role = User.Role.valueOf(resultSet.getString("role"));
				
				user = new User(id, email, password, confidentialData, role);
			}		
		} catch(Exception e) {
			//TODO: log this.
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return user;
	}
}
